﻿using MySql.Simple.Mapper.Structures;

namespace MySql.Simple.Mapper.Commands.ForeignKeys
{
    /// <summary>
    /// Command to detect the existance of foreign keys in databases.
    /// </summary>
    public static class ExistsCommand
    {
        private const string Sql = @"SELECT table_name, 
                                            column_name, 
                                            referenced_table_name, 
                                            referenced_column_name 
                                     FROM   information_schema.key_column_usage 
                                     WHERE  table_schema ='{0}' 
                                     AND    table_name = '{1}';";

        /// <summary>
        /// Determines is the specified <see cref="ForeignKey"/> exists in the underlying database.
        /// </summary>
        /// <param name="foreignKey">The foreign key.</param>
        /// <param name="connection">The connection.</param>
        /// <returns></returns>
        public static bool Execute(ForeignKey foreignKey, Connection connection)
        {
            var exists = false;

            using (var db = connection.Open())
            {
                using (var command = db.CreateCommand())
                {
                    command.CommandText = string.Format(Sql, connection.Database, foreignKey.SourceTable);

                    using (var reader = command.ExecuteReader())
                    {
                        while(reader.Read())
                        {
                            if (string.Compare(reader[0].ToString(), foreignKey.SourceTable, true) +
                                string.Compare(reader[1].ToString(), foreignKey.SourceColumn, true) +
                                string.Compare(reader[2].ToString(), foreignKey.DestinationTable, true) +
                                string.Compare(reader[3].ToString(), foreignKey.DestinationColumn, true) != 0) continue;

                            exists = true;

                            break;
                        }
                    }
                }
            }


            return exists;                
        }

    }
}